# coding=utf-8
# ☯ Author: ChinaPython
# ☯ Email : chinapython@yeah.net
# ☯ Date  : 2019/1/31 9:15
import pymysql


class SqlTool:
    '''
    sql tools for my team,it's more diy,you will love this tools;
    '''

    def __init__(self, **kwargs):
        self.connect = pymysql.connect(
            host=kwargs.get('host', 'localhost'),  # default localhost
            port=kwargs.get('port', 3306),  # default 3306
            user=kwargs.get('username', 'root'),  # default root
            password=kwargs.get('password', 'root'),  # default root
            database=kwargs.get('database', None),  # default None
            autocommit=kwargs.get('autocommit', True),  # default True
            charset=kwargs.get('charset', 'utf8'),  # default utf8
            db=kwargs.get('db', None),
            passwd=kwargs.get('passwd', None),
            unix_socket=kwargs.get('unix_socket', None),
            sql_mode=kwargs.get('sql_mode', None),
            read_default_file=kwargs.get('read_default_file', None),
            conv=kwargs.get('conv', None),
            use_unicode=kwargs.get('use_unicode', None),
            client_flag=kwargs.get('client_flag', 0),
            init_command=kwargs.get('use_unicode', None),
            connect_timeout=kwargs.get('connect_timeout', 10),
            ssl=kwargs.get('ssl', None),
            read_default_group=kwargs.get('read_default_group', None),
            compress=kwargs.get('compress', None),
            named_pipe=kwargs.get('named_pipe', None),
            local_infile=kwargs.get('local_infile', False),
            max_allowed_packet=kwargs.get('max_allowed_packet', 16777216),
            defer_connect=kwargs.get('defer_connect', False),
            auth_plugin_map=kwargs.get('auth_plugin_map', None),
            read_timeout=kwargs.get('read_timeout', None),
            write_timeout=kwargs.get('write_timeout', None),
            bind_address=kwargs.get('bind_address', None),
            binary_prefix=kwargs.get('binary_prefix', False),
            program_name=kwargs.get('program_name', None),
            server_public_key=kwargs.get('server_public_key', None)
        )
        self.cursor = self.connect.cursor()

    def insert(self, table, item):
        # need the table'name and the item,item must be dict
        self.connect.ping(reconnect=True)
        if isinstance(item, dict) is not True:
            raise Exception('TypError:The item must be "dict" ')
        if len(item) == 0:
            raise Exception('LengthError:Not support null list')
        return self.cursor.execute('''INSERT INTO {table}({keys}) VALUES({values})'''.format(
            table=table,
            keys=','.join(item.keys()),
            values=', '.join(['%s'] * len(item))
        ), list(item.values()))

    def select(self, table, field='*', query_term=None, fetchone=False):
        self.connect.ping(reconnect=True)
        self.cursor.execute('''SELECT {field} FROM {table} '''.format(
            field=field, table=table
        ) if query_term is None else '''SELECT {field} FROM {table} WHERE {query_term}'''.format(
            field=field, table=table, query_term=query_term
        ))
        return self.cursor.fetchall() if fetchone else self.cursor.fetchone()

    def update(self, table, item, query_term):
        self.connect.ping(reconnect=True)
        return self.cursor.execute('''UPDATE {table} SET {field_term} WHERE {query_term}'''.format(
            table=table,
            field_term=','.join([
                '{}="{}"'.format(k, v) if isinstance(v, str) else '{}={}'.format(k, v) for k, v in item.items()
            ]), query_term=query_term
        ))

    def delete(self, table, query_term):
        self.connect.ping(reconnect=True)
        return self.cursor.execute('''delete from {table} where {query_term}'''.format(
            table=table,
            query_term=query_term
        ))


if __name__ == '__main__':
    start = SqlTool()
